﻿Imports System.Data.SqlClient
Public Class dbSystem
    Dim Var As New varDeclare

    Public Function CurrList(ByVal Comp As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GetCurrList"
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetAllModuleList() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT * FROM tblModule WHERE mdlStatus <> 'D'"
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function FreeQueryDataSet(Query As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = Query
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function FreeQueryDataTable(Query As String) As DataTable
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = Query
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds.Tables(0)
    End Function

    Public Function FreeQueryString(Query As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = Query
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function

    Public Function GenerateMissingGLBalance() As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GenerateMissingGLBalance"
        command.Parameters.Add("@CompCode", SqlDbType.VarChar).Value = Var.CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function

    Public Function GeneratePostingPeriod() As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GeneratePostingPeriod"
        command.Parameters.Add("@CompCode", SqlDbType.VarChar).Value = Var.CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function
End Class
